﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Dapper;
using MySql.Data;
using MySql.Data.MySqlClient;



namespace EasyToHelp.DBFactory
{

    public static class DBSqlHelper
    {
        #region SqlServer链接配置
        /// <summary>
        /// 默认的Sql Server的链接字符串
        /// </summary>
        public static string connStr;

        public static IDbConnection GetSqlConnection()
        {
            IDbConnection conn = new MySqlConnection(connStr);
            conn.Open();
            return conn;
        }
        #endregion
    }
    public static class MySqlHelper
    {
        #region 判断是否存在
        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <typeparam name="T">实体名</typeparam>
        /// <param name="SqlQuery">sql语句</param>
        /// <param name="model">返回bool类型</param>
        /// <returns></returns>
        public static bool IsExist<T>(string SqlQuery, T model) where T : class
        {
            using (var db = DBSqlHelper.GetSqlConnection())
            {
                if (int.Parse(db.ExecuteScalar(SqlQuery, model).ToString()) > 0)
                {
                    return true;
                }
                return false;
            }
        }
        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="SqlQuery"></param>
        /// <returns></returns>
        public static bool IsExist(string SqlQuery)
        {
            using (var db = DBSqlHelper.GetSqlConnection())
            {
                if (db.ExecuteScalar(SqlQuery) != null)
                {
                    return true;
                }
                return false;
            }
        }
        #endregion

        #region 执行增删改
        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="SqlQuery">sql语句</param>
        /// <param name="model">实体参数</param>
        /// <returns>返回受影响的行数</returns>
        public static int Execute<T>(string SqlQuery, T model) where T : class
        {
            using (var db = DBSqlHelper.GetSqlConnection())
            {
                return db.Execute(SqlQuery, model);
            }
        }
        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="SqlQuery"></param>
        /// <returns></returns>
        public static int Execute(string SqlQuery)
        {
            try
            {
                using (var db = DBSqlHelper.GetSqlConnection())
                {
                    return db.Execute(SqlQuery);
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        #region 执行事务操作
        /// <summary>
        /// 执行增删改(事务操作)
        /// </summary>
        /// <param name="SqlQuery">sql语句</param>
        /// <returns>返回受影响的行数</returns>
        public static int Execute(List<string> SqlQuery)
        {
            int Result = 0;
            using (IDbConnection db = new SqlConnection(DBSqlHelper.connStr))
            {
                db.Open();
                IDbTransaction transaction = db.BeginTransaction();
                try
                {
                    foreach (var item in SqlQuery)
                    {
                        Result += db.Execute(item, null, transaction);
                    }
                    transaction.Commit();
                    return Result = SqlQuery.Count;
                }
                catch (Exception ex)
                {
                    ex.ToString();
                    transaction.Rollback();
                    return Result;
                }
            }

        }
        #endregion

        #region 获取实体数据
        /// <summary>
        /// 获取实体数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SqlQuery">sql语句</param>
        /// <param name="model">实体参数</param>
        /// <returns>返回实体数据</returns>
        public static T GetModel<T>(string SqlQuery, T model) where T : class
        {
            using (var db = DBSqlHelper.GetSqlConnection())
            {
                return db.QueryFirstOrDefault<T>(SqlQuery, model);
            }
        }
        #endregion

        #region 获取泛型列表数据
        /// <summary>
        /// 获取泛型列表数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SqlQuery">sql语句</param>
        /// <param name="model">实体参数</param>
        /// <returns>数据列表</returns>
        public static List<T> GetList<T>(string SqlQuery, T model = null) where T : class
        {
            using (var db = DBSqlHelper.GetSqlConnection())
            {
                return db.Query<T>(SqlQuery, model).AsList();
            }
        }
        #endregion

        #region 执行增_返回新增数据ID
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SqlQuery"></param>
        /// <param name="model"></param>
        /// <returns>返回ID</returns>
        public static int ExecuteScalar<T>(string SqlQuery, T model) where T : class
        {
            using (var db = DBSqlHelper.GetSqlConnection())
            {
                return db.ExecuteScalar<int>(SqlQuery, model);
            }
        }
        #endregion

        #region 返回DataTable
        public static DataTable GetDataTable(string SqlQuery)
        {
            using (IDbConnection conn = DBSqlHelper.GetSqlConnection())
            {
                return DataTableToIDataReader(conn.ExecuteReader(SqlQuery));
            }
        }
        public static DataTable DataTableToIDataReader(IDataReader reader)
        {
            DataTable objDataTable = new DataTable();
            int intFieldCount = reader.FieldCount;
            for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
            {
                objDataTable.Columns.Add(reader.GetName(intCounter).ToString(), reader.GetFieldType(intCounter));
            }
            objDataTable.BeginLoadData();
            object[] objValues = new object[intFieldCount];
            while (reader.Read())
            {
                reader.GetValues(objValues);
                objDataTable.LoadDataRow(objValues, true);
            }
            reader.Close();
            objDataTable.EndLoadData();
            return objDataTable;
        }
        #endregion
    }

}
